package com.two.inventory.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.two.inventory.dao.InventoryDao;
import com.two.inventory.model.Inventory;
import com.two.commodityorder.model.Commodity;
import com.two.inventory.controller.DataBaseUtil;

public  class InventoryDaoImpl implements InventoryDao{

	@Override
	public List<Inventory> selectAll() {
		List<Inventory> list = new ArrayList<>();
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from inventorymanagement where flag = 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Inventory u = new Inventory();
				u.setId(rs.getInt("id"));
				u.setInventoryname(rs.getString("inventoryname"));
				u.setPrice(rs.getString("price"));
				u.setNumber(rs.getInt("number"));
				
				list.add(u);
				u = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, rs);
		}
		return list;
	}

	
	
	public Inventory selectByInventoryName(String inventoryname) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from inventorymanagement where inventoryname = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - inventoryname: " + inventoryname);
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, inventoryname);
			rs = ps.executeQuery();
			if(rs.next()) {
				Inventory u = new Inventory();
				u.setId(rs.getInt("id"));
				u.setInventoryname(rs.getString("inventoryname"));
				u.setPrice(rs.getString("price"));
				u.setNumber(rs.getInt("number"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}



	@Override
	public int addInventory(int id, String inventoryname,String price,int number ) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "INSERT INTO `inventorymanagement` (`id`, `inventoryname`, `price`,`number` ,`flag`) VALUES (?, ?, ?, ?, 1)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, inventoryname);
			ps.setString(3, price);
			ps.setInt(4, number);
			
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public int updateInventory(int id,String inventoryname,String price,int number) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "UPDATE `inventorymanagement` SET  `inventoryname`=?, `price`=? ,`number`=? WHERE (`id`=?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, inventoryname);
			ps.setString(2, price);
			ps.setInt(3, number);
			ps.setInt(4, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	
	}



	@Override
	public int deleteInventory(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "update inventorymanagement set flag=0 where id=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public Inventory selectById(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from inventorymanagement where id = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				Inventory u = new Inventory();
				u.setId(rs.getInt("id"));
				u.setInventoryname(rs.getString("inventoryname"));
				u.setPrice(rs.getString("price"));
				u.setNumber(rs.getInt("number"));
			
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}







}